package com.jdbc;

import java.security.PublicKey;
import java.sql.*;

/**
 * @author lenovo
 * @Description
 * @Date2025-2-24 15:06
 */
public class JDBCDemo {
    public static final String URL="jdbc:mysql://localhost:3306/mysqldb?serverTimezone=GMT";
    public static final String USER ="root";
    public static final String PASSWORD="123456";

//    public static void testJDBC(){
//        //通过驱动管理器获得连接
//        try (Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);){
//            System.out.println(connection);
//            //通过连接对象获取命令对象
//            Statement statement = connection.createStatement();
//            System.out.println(statement);
//            //实现数据插入
//            String sql="insert into student_s(name,age,cellphone,address,gender) values('周2',23,'18260836302','西安','女')";
//            int rows=statement.executeUpdate(sql);
//            if(rows>0){
//                System.out.println("数据插入成功");
//            }
//
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//    }

    public static void testQuery(){
        //通过驱动管理器获得连接
        try (Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);){
            //通过连接对象获取命令对象
            Statement statement = connection.createStatement();
            //实现数据查询
            String sql="select *  from student_s";
            ResultSet resultSet= statement.executeQuery(sql);
            //遍历结果集
            while (resultSet.next()) {
                //读取每列数据
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String cellphone = resultSet.getString("cellphone");
                String address = resultSet.getString("address");
                String gender = resultSet.getString("gender");
                System.out.println("id:" + id + " name:" + name + " age:" + age + " celphone:" + cellphone + " address:" + address + " gender:" + gender);
            }


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void testSelct(){
        //通过驱动管理器获得连接
        try (Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);){
            //通过连接对象获取命令对象
            Statement statement = connection.createStatement();
            //实现数据查询
            String sql="select s.id,s.name,c.name,c.point from student_s s inner join course c on s.id=c.id";
            ResultSet resultSet= statement.executeQuery(sql);
            //遍历结果集
            while (resultSet.next()) {
                //读取每列数据
                int id = resultSet.getInt("s.id");
                String name = resultSet.getString("s.name");
                String c_name = resultSet.getString("c.name");
                String c_point = resultSet.getString("c.point");
                System.out.println("id:" + id + " s.name:" + name + " c.name:" + c_name + " c.point:" + c_point );
            }


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    public static void testPreparedStatement(){
        //通过驱动管理器获得连接
        try (Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);){
            System.out.println(connection);
            //通过连接对象获取命令对象
            String sql="insert into student_s(name,age,cellphone,address,gender) values(?,?,?,?,?)";
          PreparedStatement preparedStatement=connection.prepareStatement(sql);
            //设置参数
            preparedStatement.setString(1,"Alix");
            preparedStatement.setInt(2,26);
            preparedStatement.setString(3,"15298367281");
            preparedStatement.setString(4,"江西");
            preparedStatement.setString(5,"女");
            //执行命令
            int rows= preparedStatement.executeUpdate();
            if(rows>0){
                System.out.println("数据插入成功");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static  void  main(String[] args){
//        testJDBC();
        testQuery();
        testSelct();
        testPreparedStatement();
    }
}
